import json
import time

from django.shortcuts import render

# Create your views here.
from django.http import JsonResponse
from django.db.models import Q
from zentao.settings import PYMYSQL_CONF

from datetime import timezone
import pandas as pd
import pymysql
import numpy as np
import traceback

from apps.homepage.models import ZtAction, ZtTask, ZtBug, ZtCase,ZtStory,ZtProject,ZtProduct

import warnings
warnings.filterwarnings('ignore')

import time
from functools import wraps

def timing_decorator(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        start_time = time.time()
        result = func(*args, **kwargs)
        end_time = time.time()
        print(f"Function {func.__name__!r} executed in {(end_time - start_time)*1000:.2f}ms")
        return result
    return wrapper

#计算研发效能，只允许127.0.0.1本地调用
@timing_decorator
def week_repo(request):
    if request.method == 'GET':

        if request.META['REMOTE_ADDR'] not in ['127.0.0.1', '172.17.0.1']:
        # if request.META['REMOTE_ADDR'] != :
            return JsonResponse({'code':0,'msg':'只允许本地定时任务调用'})

        try:
            # 查找所有用户
            def get_user():
                sql = '''
                SELECT
                    u.account,
                    u.realname,
                    c.name AS company,
                    d.name AS dept,
                    g.name,
                    g.role
                FROM
                    zt_user AS u
                LEFT JOIN
                    zt_company AS c ON u.company = c.id
                LEFT JOIN
                    zt_dept AS d ON u.dept = d.id
                INNER JOIN
                    zt_usergroup AS ug ON u.account = ug.account
                INNER JOIN
                    zt_group AS g ON ug.group = g.id
                WHERE
                    NOT EXISTS (
                        SELECT 1
                        FROM zt_usergroup AS ug2
                        WHERE
                            ug2.account = u.account AND
                            ug2.group < ug.group
                    ) AND u.deleted = '0'
                '''
                # pymysql连接数据
                connection = pymysql.connect(**PYMYSQL_CONF)
                # 执行 SQL 语句
                # 连接数据库
                with connection.cursor() as cursor:
                    cursor.execute(sql)
                    get_users = cursor.fetchall()

                # 将岗位转为岗位类型
                position_map = {'开发': 'develop',
                                '开发负责人': 'develop',
                                '开发组长': 'develop',
                                '测试': 'test',
                                '测试负责人': 'test',
                                '测试组长': 'test',
                                '需求': 'story',
                                '需求负责人': 'story',
                                '需求组长': 'story',
                                '运维人员': 'om',
                                '实施人员': 'implement',
                                '咨询人员': 'ask',
                                '综合人员': 'ask',
                                '项目经理': '',
                                '产品经理': '',
                                '部门管理员': '',
                                '项目管理员': ''}

                users = []
                accounts = set()
                account2role = {}
                for user in get_users:
                    account2role[user[0]] = user[4]
                    # 这几类权限不要
                    # if user[4] in ['项目经理', '产品经理', '部门管理员', '项目管理员']:
                    #     continue
                    if user[1] in ['test_测试', 'test_测试开发主管', 'test_测试经理']:
                        continue
                    if user[0] == 'admin':
                        continue
                    # 没有部门
                    # if not user[3]:
                    #     continue
                    accounts.add(user[0])

                    # users.append(list(user))
                    users.append({
                        'account': user[0],
                        'realname': user[1],
                        'company': user[2],
                        'dept': user[3],
                        'name': user[4],
                        'positionType': position_map[user[4]]
                    })

                connection.close()

                # df = pd.DataFrame(columns=['account', 'realname', 'company', 'dept', 'name','positionType'], data=users)
                df = pd.DataFrame(users)
                # df.to_csv('user.csv', index=False)

                return users, account2role, df, accounts

            _, account2role, df_user, accounts = get_user()

            # df_user.to_excel('用户表.xlsx',index=False)

            # 查找项目产品对应表
            def get_projectproduct():
                sql = '''
                select pp.project,pp.product,p.name,p.PO, p.deleted from zt_projectproduct pp
                left join zt_product p on p.id=pp.product
                left join zt_project pr on pr.id=pp.project
                '''
                # "where p.deleted = '0' or pr.deleted = '0'"
                # '    where p.deleted='0''
                # pymysql连接数据
                connection = pymysql.connect(**PYMYSQL_CONF)
                # 执行 SQL 语句
                # 连接数据库
                with connection.cursor() as cursor:
                    cursor.execute(sql)
                    data = cursor.fetchall()

                project2product = {}
                product2name = []
                product = []
                for row in data:
                    project2product[row[0]] = {
                        'product_id': row[1],
                        'product_name': row[2],
                        'product_po': row[3]
                    }
                    if row[1] not in product:
                        product2name.append({
                            'product_id': row[1],
                            'product_name': row[2],
                            'account': row[3],
                            'deleted': row[4]
                        })
                        product.append(row[1])

                df_product = pd.DataFrame(product2name)

                return project2product, df_product

            project2product, df_product = get_projectproduct()
            df_product = pd.merge(df_product, df_user, on='account', how='left')

            # 基于产品维度
            # 需求
            # 筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
            # 搜索条件,初始化一个空的查询集
            query = Q()
            # query &= Q(projectStory_story__project__deleted='0')  # 项目不能被删除
            # query &= Q(product__deleted='0')  # 产品不能被删除
            query &= (Q(projectStory_story__project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
            # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
            query &= Q(deleted='0')  # 不删除的

            tasks = ZtStory.objects.filter(query).values('id', 'openedBy', 'product', 'changedBy', 'reviewedBy',
                                                         'projectStory_story__project__id',
                                                         'product__deleted').distinct()

            # 转为df
            data = []
            ids = []
            for task in tasks:

                # 如果产品删除了，则用项目id，否则用产品id
                if task['product__deleted'] == '1':
                    product_id = task['projectStory_story__project__id']
                else:
                    product_id = task['product']

                openedBy = task['openedBy'] if task['openedBy'] in accounts else ''
                changedBy = task['changedBy'] if task['changedBy'] in accounts else ''

                # 获取评审列表，去除空项
                reviewedBy_ls = task['reviewedBy'].split(',') if task['reviewedBy'] else []
                reviewedBy_ls = [i for i in reviewedBy_ls if i != '']

                # 只要评审人存在非删除用户，则记录
                reviewedBy = ''
                for i in reviewedBy_ls:
                    if i in accounts:
                        reviewedBy = '1'
                        break

                if task['id'] not in ids:
                    data.append([product_id, openedBy, changedBy, reviewedBy])
                    ids.append(task['id'])

            df = pd.DataFrame(columns=['product_id', 'openedBy', 'changedBy', 'reviewedBy'], data=data)

            # 筛选出操作为'acton'的行
            df_open = df[df['openedBy'] != '']
            # 计算每个人的任务指派数
            open_counts = df_open['product_id'].value_counts()
            # 筛选出操作为'acton'的行
            df_changed = df[df['changedBy'] != '']
            # 计算每个人的任务完成数
            changedBy_counts = df_changed['product_id'].value_counts()
            # 筛选出操作为'acton'的行
            df_resolvedBy = df[df['reviewedBy'] != '']
            # 计算每个人的任务指派数
            resolvedBy_counts = df_resolvedBy['product_id'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                '创建需求数': open_counts,
                '更改需求数': changedBy_counts,
                '评审需求数': resolvedBy_counts
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'index': 'product_id'})

            df_product = pd.merge(df_product, result_df, on='product_id', how='outer')

            # 任务
            # 筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
            # 搜索条件,初始化一个空的查询集
            query = Q()
            # query &= Q(project__deleted='0')  # 项目不能被删除
            # query &= Q(project__projectProduct_project__product__deleted='0')  # 产品不能被删除
            query &= (Q(project__projectProduct_project__product__deleted='0') | Q(
                project__deleted='0'))  # 项目和产品不能都被删除
            # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
            query &= Q(deleted='0')  # 不删除的
            # query &= Q(mode__in=['multi','linear'])  # 多人任务

            # tasks = ZtTask.objects.filter(query).exclude(parent=-1).select_related('assignedTo').prefetch_related('taskteam_task','taskteam_task__account').distinct()
            tasks = ZtTask.objects.filter(query).values('id', 'assignedTo__account', 'mode', 'openedBy',
                                                        'finishedBy', 'assignedTo', 'project__id',
                                                        'project__projectProduct_project__product__deleted',
                                                        'project__projectProduct_project__product__id').distinct()
            # 转为df
            data = []
            ls = []
            task_ids = []
            for task in tasks:

                # 如果产品删除了，则用项目id，否则用产品id
                if task['project__projectProduct_project__product__deleted'] == '1':
                    product_id = task['project__id']
                else:
                    product_id = task['project__projectProduct_project__product__id']

                # 统计指派任务
                if task['mode'] in ['multi', 'linear']:
                    assigned = '1'
                else:
                    if task['assignedTo']:
                        assigned = task['assignedTo'] if task['assignedTo'] in accounts else ''
                        # assigned = '1'
                    else:
                        assigned = ''

                if task['openedBy']:
                    openedBy = task['openedBy'] if task['openedBy'] in accounts else ''
                    # openedBy = '1'
                else:
                    openedBy = ''

                finishedBy = task['finishedBy'] if task['finishedBy'] in accounts else ''
                # finishedBy = '1' if task['finishedBy'] else ''
                if task['id'] not in task_ids:
                    data.append([product_id, openedBy, finishedBy, assigned])
                    ls.append([task['id'], assigned])
                    task_ids.append(task['id'])

            df_assign = pd.DataFrame(columns=['id', 'assign'], data=ls)
            df = pd.DataFrame(columns=['product_id', 'openedBy', 'finishedBy', 'assigned'], data=data)

            # 筛选出操作为'acton'的行
            df_open = df[df['openedBy'] != '']
            # 计算每个人的任务指派数
            open_counts = df_open['product_id'].value_counts()
            # 筛选出操作为'acton'的行
            df_finishedBy = df[df['finishedBy'] != '']
            # 计算每个人的任务完成数
            finishedBy_counts = df_finishedBy['product_id'].value_counts()
            # 筛选出操作为'acton'的行
            df_assigned = df[df['assigned'] != '']
            # 计算每个人的任务指派数
            assigned_counts = df_assigned['product_id'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                '创建任务数': open_counts,
                '指派任务数': assigned_counts,
                '完成任务数': finishedBy_counts
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'index': 'product_id'})

            df_product = pd.merge(df_product, result_df, on=['product_id'], how='outer')

            # bug

            # 筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
            # 搜索条件,初始化一个空的查询集
            query = Q()
            query &= (Q(project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
            # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
            query &= Q(deleted='0')  # 不删除的
            # query &= Q(mode__in=['multi','linear'])  # 多人任务

            bugs = ZtBug.objects.filter(query).values('id', 'product', 'project__id', 'product__deleted',
                                                      'openedBy', 'assignedTo', 'confirmed', 'resolvedBy',
                                                      'closedBy').distinct()

            # 转为df
            data = []
            ids = []
            for bug in bugs:

                # 如果产品删除了，则用项目id，否则用产品id
                if bug['product__deleted'] == '1':
                    product_id = bug['project__id']
                else:
                    product_id = bug['product']

                openedBy = bug['openedBy'] if bug['openedBy'] in accounts else ''
                assignedTo = bug['assignedTo'] if bug['assignedTo'] in accounts else ''
                resolvedBy = bug['resolvedBy'] if bug['resolvedBy'] in accounts else ''
                closedBy = bug['closedBy'] if bug['closedBy'] in accounts else ''

                if bug['confirmed'] == 1:
                    c = assignedTo
                else:
                    c = ''

                # data.append([product_id,bug['openedBy'], bug['assignedTo'], bug['resolvedBy'], c, bug['closedBy']])

                if bug['id'] not in ids:
                    data.append([product_id, openedBy, assignedTo, resolvedBy, c, closedBy])
                    ids.append(bug['id'])

            df = pd.DataFrame(
                columns=['product_id', 'openedBy', 'assignedTo', 'resolvedBy', 'confirmed', 'closedBy'], data=data)
            # df.to_csv('bug.csv',index=False)

            # 筛选出操作为'acton'的行
            df_open = df[df['openedBy'] != '']
            # 计算每个人的任务指派数
            open_counts = df_open['product_id'].value_counts()
            # 筛选出操作为'acton'的行
            df_assigned = df[df['assignedTo'] != '']
            # 计算每个人的任务完成数
            assigned_counts = df_assigned['product_id'].value_counts()
            # 筛选出操作为'acton'的行
            df_resolvedBy = df[df['resolvedBy'] != '']
            # 计算每个人的任务指派数
            resolvedBy_counts = df_resolvedBy['product_id'].value_counts()
            # 筛选出操作为'acton'的行
            df_confirmed = df[df['confirmed'] != '']
            # 计算每个人的任务指派数
            confirmed_counts = df_confirmed['product_id'].value_counts()
            # 筛选出操作为'acton'的行
            df_close = df[df['closedBy'] != '']
            # 计算每个人的任务完成数
            close_counts = df_close['product_id'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                '创建缺陷数': open_counts,
                '指派缺陷数': assigned_counts,
                '确认缺陷数': confirmed_counts,
                '解决缺陷数': resolvedBy_counts,
                '关闭缺陷数': close_counts
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'index': 'product_id'})

            df_product = pd.merge(df_product, result_df, on='product_id', how='outer')

            # 测试用例
            # 筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
            # 搜索条件,初始化一个空的查询集
            query = Q()
            query &= (Q(project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
            # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
            query &= Q(deleted='0')  # 不删除的
            # query &= Q(mode__in=['multi','linear'])  # 多人任务

            cases = ZtCase.objects.filter(query).values('id', 'product', 'project__id', 'product__deleted',
                                                        'openedBy', 'lastRunner').distinct()

            # 转为df
            data = []
            ids = []
            for case in cases:

                # 如果产品删除了，则用项目id，否则用产品id
                if case['product__deleted'] == '1':
                    product_id = case['project__id']
                else:
                    product_id = case['product']

                openedBy = case['openedBy'] if case['openedBy'] in accounts else ''
                lastRunner = case['lastRunner'] if case['lastRunner'] in accounts else ''

                if case['id'] not in ids:
                    data.append([product_id, openedBy, lastRunner])
                    ids.append(case['id'])

            df = pd.DataFrame(columns=['product_id', 'openedBy', 'lastRunner'], data=data)
            # df.to_csv('bug.csv',index=False)

            # 筛选出操作为'acton'的行
            df_open = df[df['openedBy'] != '']
            # 计算每个人的任务指派数
            open_counts = df_open['product_id'].value_counts()
            # 筛选出操作为'acton'的行
            df_assigned = df[df['lastRunner'] != '']
            # 计算每个人的任务完成数
            assigned_counts = df_assigned['product_id'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                '创建用例数': open_counts,
                '执行用例数': assigned_counts,
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'index': 'product_id'})

            df_product = pd.merge(df_product, result_df, on='product_id', how='outer')

            # 获取项目id，即产品名称为空的
            df_project = df_product[df_product['product_name'].isna()]
            project_id = list(df_project['product_id'])

            # 统计项目id对应的名称负责人等
            data = []
            projects = ZtProject.objects.filter(id__in=project_id).values('id', 'name', 'PM').distinct()
            for project in projects:
                data.append(
                    {'product_id': project['id'], 'product_name': project['name'], 'account': project['PM']})
            # 转为df并拼接
            df_project = pd.DataFrame(data)
            df_project = pd.merge(df_project, df_user, on='account', how='left')

            project_id2detail = {}
            # 定义映射规则
            for row in df_project.itertuples():
                project_id2detail[row[1]] = {'产品名称': row[2],
                                             '用户名': row[3],
                                             '姓名': row[5],
                                             '部门': row[7]}

            # 统计项目id对应的名称负责人等
            data1 = []
            projects = ZtProduct.objects.all().values('id', 'name', 'PO', 'deleted').distinct()
            for project in projects:
                data.append({'product_id': project['id'], 'product_name': project['name'], 'account': project['PO'],
                             'deleted': project['deleted']})
            # 转为df并拼接
            df_project = pd.DataFrame(data)
            df_project = pd.merge(df_project, df_user, on='account', how='left')

            project_id2detail1 = {}
            # 定义映射规则
            for row in df_project.itertuples():
                project_id2detail1[row[1]] = {'产品名称': row[2],
                                              '用户名': row[3],
                                              '姓名': row[5],
                                              '部门': row[7]}

            # 转换列名
            df_product = df_product.rename(columns={'product_id': '产品id',
                                                    'product_name': '产品名称',
                                                    'account': '用户名',
                                                    'realname': '姓名',
                                                    'company': '公司',
                                                    'dept': '部门',
                                                    'name': '岗位',
                                                    'positionType': '岗位类型', })

            df_product = df_product.drop(['公司', '岗位', '岗位类型'], axis=1)

            # 应用映射规则
            for key, cols in project_id2detail.items():
                mask = df_product['产品id'] == key
                for col_name, new_value in cols.items():
                    df_product.loc[mask, col_name] = new_value

            # 应用映射规则
            for key, cols in project_id2detail1.items():
                mask = df_product['产品id'] == key
                for col_name, new_value in cols.items():
                    df_product.loc[mask, col_name] = new_value

            # 获取后五列的列名
            last_five_columns = df_product.columns[-13:]

            # 将后五列的空值填充为 0
            df_product[last_five_columns] = df_product[last_five_columns].fillna(0).astype(int)

            df_product['deleted'] = df_product['deleted'].fillna('0').astype(str)

            df_product = df_product[df_product['deleted'].isin(['0', ''])]
            df_product.to_excel('禅道数据统计_产品维度.xlsx', index=False)

            # 基于个人维度
            # 查找当月指派任务和以前未完成的
            # 筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
            # 搜索条件,初始化一个空的查询集
            query = Q()
            query &= (Q(projectStory_story__project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
            # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
            query &= Q(deleted='0')  # 不删除的

            tasks = ZtStory.objects.filter(query).values('id', 'openedBy', 'product', 'changedBy',
                                                         'reviewedBy').distinct()

            # 转为df
            data = []
            for task in tasks:
                data.append([task['openedBy']])

            df = pd.DataFrame(columns=['openedBy'], data=data)
            # df.to_csv('bug.csv',index=False)

            # 计算每个人的需求变更数
            reviewed_counts = df['openedBy'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                '创建需求数': reviewed_counts
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'openedBy': 'account'})

            df_user = pd.merge(df_user, result_df, on='account', how='left')

            # 查找当月需求的操作记录

            # 有产品和项目的id
            query = Q()
            query &= (Q(projectStory_story__project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
            # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
            query &= Q(deleted='0')  # 不删除的

            story_ids = ZtStory.objects.filter(query).values_list('id').distinct()

            # 搜索条件,初始化一个空的查询集
            query = Q()
            query &= Q(objectType='story')  # 只要需求相关的
            query &= Q(objectID__in=story_ids)  # 只要项目和产品不能都被删除的需求

            actions = ZtAction.objects.filter(query).values('id', 'objectType', 'objectID', 'actor',
                                                            'action').distinct()

            # 转为df
            data = []
            for action in actions:
                data.append([action['objectID'], action['objectType'], action['actor'], action['action']])

            df = pd.DataFrame(columns=['objectID', 'objectType', 'actor', 'action'], data=data)

            # df.to_csv('story.csv',index=False)

            # 筛选出操作为'acton'的行
            df_changed = df[df['action'] == 'changed']
            # 计算每个人的需求变更数
            changed_counts = df_changed['actor'].value_counts()

            # 筛选出操作为'acton'的行
            df_reviewed = df[df['action'] == 'reviewed']
            # 计算每个人的需求变更数
            reviewed_counts = df_reviewed['actor'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                # '需求创建数': open_counts,
                '需求变更数': changed_counts,
                '需求评审数': reviewed_counts,
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'actor': 'account'})

            df_user = pd.merge(df_user, result_df, on='account', how='left')

            # 查找当月指派任务和以前未完成的
            # 筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
            # 搜索条件,初始化一个空的查询集
            query = Q()
            query &= (Q(project__projectProduct_project__product__deleted='0') | Q(
                project__deleted='0'))  # 项目和产品不能都被删除
            # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
            query &= Q(deleted='0')  # 不删除的

            # tasks = ZtTask.objects.filter(query).exclude(parent=-1).values('openedBy')
            tasks = ZtTask.objects.filter(query).values('id', 'openedBy').distinct()

            # 转为df
            data = []
            for task in tasks:
                data.append([task['openedBy']])

            df = pd.DataFrame(columns=['openedBy'], data=data)
            # df.to_csv('bug.csv',index=False)

            # 计算每个人的需求变更数
            reviewed_counts = df['openedBy'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                '创建任务数': reviewed_counts
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'openedBy': 'account'})

            df_user = pd.merge(df_user, result_df, on='account', how='left')

            # 查找指派任务
            # 筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
            # 搜索条件,初始化一个空的查询集
            query = Q()
            query &= (Q(project__projectProduct_project__product__deleted='0') | Q(
                project__deleted='0'))  # 项目和产品不能都被删除
            # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
            query &= Q(deleted='0')  # 不删除的
            # query &= Q(mode__in=['multi','linear'])  # 多人任务

            # tasks = ZtTask.objects.filter(query).exclude(parent=-1).select_related('assignedTo').prefetch_related('taskteam_task','taskteam_task__account').distinct()
            tasks = ZtTask.objects.filter(query).select_related('assignedTo').prefetch_related('taskteam_task',
                                                                                               'taskteam_task__account').distinct()

            # 转为df
            data = []
            ls = []
            for task in tasks:

                # 获取指派人，如果是多人任务则拼接，否则使用指派人
                team_list = task.taskteam_task.all()  # 获取该任务所有人
                if team_list:
                    for person in team_list:
                        # data.append([task.id,person.account.account,assignedDate])
                        data.append([person.account.account])
                        ls.append([task.id, person.account.account])
                else:
                    if task.assignedTo:
                        a = task.assignedTo.account
                    else:
                        a = ''
                    data.append([a])
                    ls.append([task.id, a])

            df_assign = pd.DataFrame(columns=['id', 'assign'], data=ls)

            # df = pd.DataFrame(columns=['id','assignedTo','assignedDate'], data=data)
            df = pd.DataFrame(columns=['assignedTo'], data=data)
            df[['assignedTo']] = df[['assignedTo']].fillna('').astype(str)

            # 计算每个人的需求变更数
            reviewed_counts = df['assignedTo'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                '指派任务数': reviewed_counts
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'assignedTo': 'account'})

            df_user = pd.merge(df_user, result_df, on='account', how='left')

            # 查找当月的完成任务

            # 筛选多人任务，使用zt_task，单人任务使用action筛选，只要最后一个指派人
            # 搜索条件,初始化一个空的查询集
            query = Q()
            query &= (Q(project__projectProduct_project__product__deleted='0') | Q(
                project__deleted='0'))  # 项目和产品不能都被删除
            # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
            query &= Q(deleted='0')  # 不删除的
            # query &= Q(mode__in=['multi','linear'])  # 多人任务

            # tasks = ZtTask.objects.filter(query).exclude(parent=-1).select_related('assignedTo').prefetch_related('taskteam_task','taskteam_task__account').distinct()
            tasks = ZtTask.objects.filter(query).select_related('assignedTo').prefetch_related('taskteam_task',
                                                                                               'taskteam_task__account').distinct()

            finish_task = {}
            # 转为df
            # task_accounts = {}   #多人任务人员列表
            data = []
            for task in tasks:

                # 获取指派人，如果是多人任务则拼接，否则使用指派人
                team_list = task.taskteam_task.all()  # 获取该任务所有人
                if team_list:
                    # task_accounts[task.id] = []
                    for person in team_list:
                        if person.status == 'done':
                            # data.append([task.id,person.account.account,assignedDate])
                            #     finish_task[f"{task.id}{person.account.account}"] = [task.id,person.account.account]
                            data.append([task.id, person.account.account])
                else:
                    if task.status in ['done', 'closed']:
                        if task.assignedTo:
                            a = task.assignedTo.account
                        else:
                            a = ''
                        # finish_task[task.id] = [task.id, a]
                        data.append([task.id, a])

            # 查找父节点的任务，不需要
            # 搜索条件,初始化一个空的查询集
            # query = Q()
            # query &= Q(deleted='0')  # 不删除的
            # query &= Q(parent=-1)  # 父节点
            #
            # parent_tasks = ZtTask.objects.filter(query).values_list('id').distinct()
            #
            # # 搜索条件,初始化一个空的查询集
            # query = Q()
            # query &= Q(objectType='task')  # 只要任务相关的
            # query &= Q(action='finished')  # 只要完成的
            #
            # actions = ZtAction.objects.filter(query).exclude(objectID__in=parent_tasks).values('objectID', 'actor')
            #
            # #统计完成人和任务
            # # data = []
            #
            #
            # for action in actions:
            #
            #     #如果是多人任务，判断是否在团队成员中，在的话才算完成，否则直接统计
            #     if action['objectID'] in task_accounts.keys():
            #         if action['actor'] in task_accounts[action['objectID']]:
            #             finish_task[f"{action['objectID']}{action['actor']}"] = [action['objectID'],action['actor']]
            #     else:
            #         finish_task[action['objectID']] = [action['objectID'],action['actor']]

            df = pd.DataFrame(columns=['id', 'finishedBy'], data=data)

            # 删除列
            df = df.drop(['id'], axis=1)

            # 计算每个人的需求变更数
            reviewed_counts = df['finishedBy'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                '完成任务数': reviewed_counts
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'finishedBy': 'account'})

            df_user = pd.merge(df_user, result_df, on='account', how='left')

            # 查找当月提交的bug（缺陷）
            # 搜索条件,初始化一个空的查询集
            query = Q()
            query &= (Q(project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
            query &= Q(deleted='0')  # 不删除的

            bugs = ZtBug.objects.filter(query).values('id', 'openedBy', 'assignedTo', 'confirmed', 'resolvedBy',
                                                      'closedBy').distinct()

            # 转为df
            data = []
            for bug in bugs:

                if bug['confirmed'] == 1:
                    c = bug['assignedTo']
                else:
                    c = ''

                data.append([bug['openedBy'], bug['assignedTo'], bug['resolvedBy'], c, bug['closedBy']])

            df = pd.DataFrame(columns=['openedBy', 'assignedTo', 'resolvedBy', 'confirmed', 'closedBy'], data=data)
            # df.to_csv('bug.csv',index=False)

            # 计算每个人的任务指派数
            open_counts = df['openedBy'].value_counts()
            # 计算每个人的任务完成数
            assigned_counts = df['assignedTo'].value_counts()
            # 计算每个人的任务指派数
            resolvedBy_counts = df['resolvedBy'].value_counts()
            # 计算每个人的任务完成数
            confirmed_counts = df['confirmed'].value_counts()
            # 计算每个人的任务指派数
            close_counts = df['closedBy'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                '创建缺陷数': open_counts,
                '指派缺陷数': assigned_counts,
                '确认缺陷数': confirmed_counts,
                '解决缺陷数': resolvedBy_counts,
                '关闭缺陷数': close_counts
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'index': 'account'})

            df_user = pd.merge(df_user, result_df, on='account', how='left')

            # 查找当月提交的bug（缺陷）

            # 搜索条件,初始化一个空的查询集
            query = Q()
            query &= (Q(project__deleted='0') | Q(product__deleted='0'))  # 项目和产品不能都被删除
            query &= Q(deleted='0')  # 不删除的

            bugs = ZtCase.objects.filter(query).values('id', 'openedBy', 'lastRunner').distinct()

            # 转为df
            data = []
            for bug in bugs:
                data.append([bug['openedBy'], bug['lastRunner']])

            df = pd.DataFrame(columns=['openedBy', 'lastRunner'], data=data)
            # df.to_csv('bug.csv',index=False)

            # 计算每个人的任务指派数
            open_counts = df['openedBy'].value_counts()
            # 计算每个人的任务完成数
            lastRunner_counts = df['lastRunner'].value_counts()

            # 将结果合并为一个DataFrame
            result_df = pd.DataFrame({
                '创建用例数': open_counts,
                '执行用例数': lastRunner_counts,
            }).fillna(0).astype(int)

            # 使用reset_index()方法将索引转换为列
            result_df = result_df.reset_index()

            # 如果你想要给这个新列一个特定的名称，可以使用rename()方法
            result_df = result_df.rename(columns={'index': 'account'})

            df_user = pd.merge(df_user, result_df, on='account', how='left')

            # 转换列名
            df_user = df_user.rename(columns={'account': '用户名',
                                              'realname': '姓名',
                                              'company': '公司',
                                              'dept': '部门',
                                              'name': '岗位',
                                              'positionType': '岗位类型', })

            _type_map = {'general': '通用',
                         'develop': '开发',
                         'test': '测试',
                         'story': '需求',
                         'om': '运维',
                         'implement': '实施',
                         'ask': '综合/咨询'}

            df_user['岗位类型'] = df_user['岗位类型'].map(_type_map)

            df_user.to_excel('禅道数据统计_个人维度.xlsx', index=False)

            res = {
                'code': 1,
                'msg': "计算成功"
            }
            return JsonResponse(res)
        except:
            res = {
                'code': 0,
                'msg': '计算失败'
            }
            print(traceback.format_exc())
            return JsonResponse(res)

    #不是post请求
    res = {
        'code': 0,
        'msg': "不支持的请求方法"
    }
    return JsonResponse(res, status=405)





# 针对完成的action和需求的创建、变更及评审和任务完成进行补充
# current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
#
# have_action = []
# actions = ZtAction.objects.filter(objectType__in=['story','task']).values('objectType','action','objectID','actor').all()
#
# for action in actions:
#     if action['objectType'] == 'task' and action['action'] == 'finished':
#         have_action.append(f'task{action["objectID"]}{action["actor"]}finished')
#
#     if action['objectType'] == 'story' and action['action'] in ['opened','reviewed','changed']:
#         have_action.append(f'story{action["objectID"]}{action["actor"]}{action["action"]}')
#
#
# #任务
# # 搜索条件,初始化一个空的查询集
# query = Q()
# # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
# query &= Q(deleted='0')  # 不删除的
# # query &= Q(mode__in=['multi','linear'])  # 多人任务
#
# tasks = ZtTask.objects.filter(query).select_related('assignedTo','execution','project').prefetch_related('taskteam_task','taskteam_task__account').distinct()
#
# ss = 0
#
# #转为df
# data = []
# task_ids = set()   #未完成人员列表
# for task in tasks:
#
#     # 如果没有完成时间，则用现在时间替代
#     if not task.finishedDate:
#         finishedDate = current_time
#     else:
#         finishedDate = task.finishedDate.astimezone(timezone.utc).replace(tzinfo=None)
#
#     # 对应的product
#     if task.project in project2product.keys():
#         product = project2product[task.project.id]['product_id']
#     else:
#         product=0
#
#     #获取指派人，如果是多人任务则拼接，否则使用指派人
#     team_list = task.taskteam_task.all()  # 获取该任务所有人
#     if team_list:
#         for person in team_list:
#
#             if person.status == 'done':
#                 if f'task{task.id}{person.account.account}finished' not in have_action:
#                     data.append(('task',task.id,product,task.project.id,task.execution.id,person.account.account,'finished',finishedDate,'', '', '0', 'rnd', 0))
#     else:
#         if task.status in ['done','closed']:
#             if task.finishedBy:
#                 if f'task{task.id}{task.finishedBy}finished' not in have_action:
#                     data.append(('task', task.id, product, task.project.id, task.execution.id,
#                                  task.finishedBy, 'finished', finishedDate, '', '', '0', 'rnd', 0))
#
#
# #需求
#
# #查找项目产品对应表
# def get_storyproject():
#     sql = '''
#     select project,story from zt_projectstory
#     '''
#     # '    where p.deleted='0''
#     # pymysql连接数据
#     connection = pymysql.connect(**PYMYSQL_CONF)
#     # 执行 SQL 语句
#     # 连接数据库
#     with connection.cursor() as cursor:
#         cursor.execute(sql)
#         data = cursor.fetchall()
#
#     story2project = {}
#     for row in data:
#         story2project[row[1]] = row[0]
#
#     return story2project
#
# story2project = get_storyproject()
#
# def get_storyreview():
#     res = {}
#     sql = '''
#     select story,reviewer,reviewDate from zt_storyreview where result='pass'
#     '''
#     # '    where p.deleted='0''
#     # pymysql连接数据
#     connection = pymysql.connect(**PYMYSQL_CONF)
#     # 执行 SQL 语句
#     # 连接数据库
#     with connection.cursor() as cursor:
#         cursor.execute(sql)
#         data = cursor.fetchall()
#
#     for row in data:
#
#         if row[0] not in res.keys():
#             res[row[0]] = []
#
#         # 如果没有完成时间，则用现在时间替代
#         if not row[2]:
#             reviewedDate = current_time
#         else:
#             reviewedDate = row[2].astimezone(timezone.utc).replace(tzinfo=None)
#
#         # 获取评审人
#         if row[1]:
#             if f'story{row[0]}{row[1]}reviewed' not in have_action:
#                 res[row[0]].append(
#                     [0, row[1], 'reviewed', reviewedDate, '', 'Pass',
#                      '0', 'rnd', 0])
#     return res
#
# storyreview = get_storyreview()
#
# # 搜索条件,初始化一个空的查询集
# query = Q()
# # query &= (Q(assignedDate__range=(start_date, current_time)) | Q(openedDate__range=(start_date, current_time))) # 当月指派时间
# query &= Q(deleted='0')  # 不删除的
# # query &= Q(mode__in=['multi','linear'])  # 多人任务
#
# tasks = ZtStory.objects.filter(query).select_related('product').distinct()
#
# #转为df
# data1 = []
# data2= []
# data3= []
# for story in tasks:
#
#     # 如果没有完成时间，则用现在时间替代
#     if not story.openedDate:
#         openedDate = current_time
#     else:
#         openedDate = story.openedDate.astimezone(timezone.utc).replace(tzinfo=None)
#
#     # 如果没有完成时间，则用现在时间替代
#     if not story.changedDate:
#         changedDate = current_time
#     else:
#         changedDate = story.changedDate.astimezone(timezone.utc).replace(tzinfo=None)
#
#     # 对应的product
#     if story.id in story2project.keys():
#         project = story2project[story.id]
#     else:
#         project=0
#
#     if action['objectType'] == 'story' and action['action'] in ['opened','reviewed','changed']:
#         have_action.append(f'story{action["objectID"]}{action["actor"]}{action["action"]}')
#
#     #获取创建人
#     if story.openedBy:
#         if f'story{story.id}{story.openedBy}opened' not in have_action:
#             data1.append(('story',story.id,story.product.id,project,0,story.openedBy,'opened',openedDate,'', '', '0', 'rnd', 0))
#
#     # 获取变更人
#     if story.changedBy:
#         if f'story{story.id}{story.changedBy}changed' not in have_action:
#             data1.append(('story', story.id, story.product.id, project, 0, story.changedBy, 'changed', changedDate, '', '',
#                          '0', 'rnd', 0))
#             data2.append(1)
#
#     # 获取评审人
#     if story.id in storyreview.keys():
#         for review in storyreview[story.id]:
#             if f'story{story.id}{review[1]}reviewed' not in have_action:
#                 data1.append(
#                     tuple(['story', story.id, story.product.id, project]+review))
#                 data3.append(1)
#
# data = data+data1
#
# def pymysql_import(connection, sql, values):
#     # 连接数据库
#     with connection.cursor() as cursor:
#
#         # 执行 SQL 语句
#         cursor.execute(sql, values)
#
#         # 提交事务
#         connection.commit()
#
#         return cursor.lastrowid
#
# connection = pymysql.connect(**PYMYSQL_CONF)
# # 标识为插入
# sql = "INSERT INTO `zt_action` " \
#       "(`objectType`, `objectID`, `product`, `project`, `execution`, `actor`, `action`,`date`," \
#       "`comment`,`extra`,`read`,`vision`,`efforted`)" \
#       " VALUES " \
#       "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
# # 要插入的数据
# l = len(data)
#
# cursor = connection.cursor()
# for num,i in enumerate(data,start=1):
#
#     print(num,l)
#     pymysql_import(connection, sql, i)